﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Configuration;
using System.Data;
using System.Data.SqlClient;

/**
 * 
 * Class Name: DataHelper
 * Description: This class contains all static methods that will assist the operation in Data Access Layer particularly
 *              in sending and receiving message to the database server.
 * Author: John Baguio
 * Date Last Modified: 02/27/2012
 * 
 * */
namespace DataHelper
{
    public class DataHelper
    {
        //get connection string set from application configuration file and return
        private static string get_constring() { return System.Configuration.ConfigurationManager.ConnectionStrings["swertress_main"].ConnectionString; }

        //open sql connection and return the connection object
        private static SqlConnection open() 
        {
            SqlConnection conn = new SqlConnection(get_constring());
            conn.Open();
            return conn;
        }

        //close open sql connection
        private static void close(SqlConnection conn) { conn.Close(); }
        
        //TODO: ado.net command builder
        public static SqlCommand build_command(string command_text, object[][] param = null)
        {
            SqlCommand cmd = new SqlCommand(command_text);
            //open connection
            SqlConnection conn = DataHelper.open();
            cmd.Connection = conn;
            cmd.CommandType = CommandType.StoredProcedure;
            if (param != null)
            {
                //add parameter
                SqlParameterCollection _params = DataHelper.build_command_parameters(param);
                foreach (SqlParameter _param in _params)
                {
                    SqlParameter temp = new SqlParameter();
                    //name of parameter variable
                    temp.ParameterName = _param.ParameterName;
                    //data type of the parameter
                    temp.SqlDbType = _param.SqlDbType;
                    //set value of the parameter
                    temp.Value = _param.Value;
                    //set direction of the parameter (input, output, input and output)
                    temp.Direction = _param.Direction;
                    //add parameter to command
                    cmd.Parameters.Add(_param);                    
                }
            }                     
            //execute command
            cmd.ExecuteNonQuery();
            //close connection
            DataHelper.close(conn);
            return cmd;
        }

        //TODO: ado.net command parameters builder
        private static SqlParameterCollection build_command_parameters(object[][] param)
        {
            SqlParameterCollection _params = new SqlCommand().Parameters;
            if (param != null)
            {
                for (int row = 0; row < param.GetUpperBound(0); row++)
                {
                    SqlParameter _param = new SqlParameter();
                    for (int col = 0; col < param.GetUpperBound(0); col++)
                    {                        
                        //parameter name
                        if (col == 0) { _param.ParameterName = param[row][col].ToString(); }
                        //sqldbtype
                        if (col == 1) { _param.SqlDbType = (SqlDbType)param[row][col]; }
                        //parameter value
                        if (col == 2) { _param.Value = param[row][col]; }
                        //parameter direction            
                        if (col == 3)
                        {
                            switch ((int)param[row][col])
                            {
                                case 0: _param.Direction = ParameterDirection.Input;
                                    break;
                                case 1: _param.Direction = ParameterDirection.Output;
                                    break;
                                case 2: _param.Direction = ParameterDirection.InputOutput;
                                    break;
                            }
                        }
                    }
                    _params.Add(_param);
                }
            }
            return _params;
        }

        //TODO: fill dataset
        public static DataSet fill_dataset(SqlCommand cmd, string dataset_name)
        {            
            //return value
            DataSet ds = new DataSet(dataset_name);
            //object use to fill dataset
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(ds);
            return ds;
        }

        //TODO: fill dataset
        public static DataSet fill_dataset(DataSet ds, SqlCommand cmd, string dataset_name)
        {            
            //object use to fill dataset
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(ds, dataset_name); //do fill            
            return ds;
        }

    }
}
